import pymysql
conn = pymysql.connect(host="47.100.16.211", port=3306, user="root",password="Yjz81808261",database="cartoon_spider",charset="utf8")
insert_str = "INSERT INTO `t_book` (`bid`, `catid`, `click`, `zan`, `bookname`, `author`, `litpic`, `lispicd`, `description`, `title`, `contentlitpic`, `addtime`) " \
             "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"

insert_chapter = " INSERT INTO `t_chapter` (`bid`, `chapter_id`, `chapter_name`, `num`, `contents`) VALUES (%s, %s, %s, %s, %s);"

def getAllCartoon():
    cursor = conn.cursor()
    sql = "select * from t_book"
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

def getCartoonById(bid):
    cursor = conn.cursor()
    sql = "select * from t_book where bid= '%s'"
    cursor.execute(sql %bid)
    result = cursor.fetchone()
    cursor.close()
    return result

def getChapterById(chapterId):
    cursor = conn.cursor()
    sql = "select * from t_chapter where chapter_id = '%s'"
    cursor.execute(sql %chapterId)
    result = cursor.fetchone()
    cursor.close()
    return result

def selectChaptersByIdBiggerThenNum(chapterId, chapterNum):
    cursor = conn.cursor()
    sql = "select * from t_chapter where bid = '%s' and num > %s"
    cursor.execute(sql %(chapterId, chapterNum))
    result = cursor.fetchall()
    cursor.close()
    return result

def getChapterMaxNumById(cartoonId):
    cursor = conn.cursor()
    sql = "select max(num) from t_chapter where bid= '%s'"
    cursor.execute(sql %cartoonId)
    result = cursor.fetchone()
    cursor.close()
    return result

def getLatestChapter(cartoonId):
    cursor = conn.cursor()
    sql = "select * from t_chapter where bid= '%s' order by num desc limit 1"
    cursor.execute(sql %cartoonId)
    result = cursor.fetchone()
    cursor.close()
    return result

def insertCartoon(book):
    cursor = conn.cursor()
    # 执行SQL语句
    cursor.execute(insert_str, [book['bid'], book['catid'],book['click'],book['zan'],book['bookname'],book['author'],book['litpic'],book['litpicd'],book['description'], book['title'], book['contentlitpic'],book['addtime']])
    # 提交事务
    conn.commit()
    cursor.close()
# chapter = {"chapter_id": "", "title": '', 'num': '', 'content': []}

def insertChapter(chapter):
    cursor = conn.cursor()
    # 执行SQL语句
    result = cursor.execute(insert_chapter, [chapter['bid'], chapter["chapter_id"], chapter["title"], chapter["num"], ",".join(chapter["content"])])
    # 提交事务
    conn.commit()
    cursor.close()
    return result;